import os
os.environ['PYTHONIOENCODING'] = 'utf-8'
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text
from flask_migrate import Migrate
app=Flask(__name__)

# 设置连接数据库的信息
HOSTNAME='127.0.0.1'
PORT=3306
USERNAME='root'
PASSWORD='123456'
DATABASE='database_learn'

# 设置连接数据库的URL
app.config['SQLALCHEMY_DATABASE_URI']=f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8mb4'
app.config['JSON_AS_ASCII'] = False

# 在app.config中设置好连接数据库的信息，然后使用SQLLichemy(app)创建一个db对象
# SQLAlchemy会自动读取app.config中连接数据库的信息

db=SQLAlchemy(app)
# 将app和db关联起来
migrate=Migrate(app,db)

# with app.app_context():
#     with db.engine.connect() as conn:
#         result=conn.execute(text("select 1"))
#         print(result.fetchone())

class User(db.Model):
    __tablename__='user'
    id=db.Column(db.Integer,primary_key=True,autoincrement=True)
    username=db.Column(db.String(100),nullable=False)
    password=db.Column(db.String(100),nullable=False)
    email =db.Column(db.String(100),nullable=False)
    age=db.Column(db.Integer,default=0)

# user = User(username="法外狂徒张三",password="123456")

class Article(db.Model):
    __tablename__='article'
    id=db.Column(db.Integer,primary_key=True,autoincrement=True)
    title=db.Column(db.String(200),nullable=False)
    content=db.Column(db.Text,nullable=False)
    # 外键
    author_id=db.Column(db.Integer,db.ForeignKey('user.id'))
    # 反向引用
    author=db.relationship('User',backref='articles')

article=Article(title="Flask学习大纲",content="Flaskxxxxx")


with app.app_context():
    db.create_all()

@app.route('/')
def hello_world():
    return 'Hello World!'

@app.route('/user/add')
def add_user():
    user1=User(username="法外狂徒张三",password="123456")
    user2=User(username="llf",password="123456")
    # 将user对象添加到session中
    db.session.add(user1)
    db.session.add(user2)
    # 将session中的数据提交到数据库中
    db.session.commit()
    return '添加用户成功'

@app.route('/user/query')
def query_user():
    # 查询所有用户
    # users=User.query.all()
    # for user in users:
    #     print(f"{user.id}:{user.username}--{user.password}".encode('utf-8').decode('utf-8'))
    # 查询id为1的用户
    # user=User.query.get(1)
    # print(f"{user.id}:{user.username}--{user.password}".encode('utf-8').decode('utf-8'))
    # 查询username为llf的用户
    user=User.query.filter(User.username=="llf").first()
    print(f"{user.id}:{user.username}--{user.password}".encode('utf-8').decode('utf-8'))
    return '查询用户成功'

@app.route('/user/update')
def update_user():
    user=User.query.get(1)
    user.username="法外狂徒李四"
    db.session.commit()
    return '修改用户成功'

@app.route('/user/delete')
def delete_user():
    user=User.query.get(1)
    db.session.delete(user)
    db.session.commit()
    return '删除用户成功'

@app.route('/article/add')
def add_article():
    article1=Article(title="Flask学习大纲",content="Flaskxxxxx")
    article1.author=User.query.get(2)

    article2=Article(title="Django学习大纲",content="Djangoxxxxx")
    article2.author=User.query.get(3)

    db.session.add(article1)
    db.session.add(article2)
    db.session.commit()
    return '添加文章成功'

@app.route('/article/query')
def query_article():
    user=User.query.get(2)
    for article in user.articles:
        print(article.title)
    return '查询文章成功'

if __name__ == '__main__':
    app.run(debug=True)
